﻿using System;
using System.Collections.Generic;
using System.Text;
using LibraryONTrack.dto;
using System.Data.SQLite;

namespace LibraryONTrack.dao
{
	public class PersonDAO : BaseDAO, IBaseDAO<Person>
	{
		public List<Person> GetAll()
		{
			string query = @"SELECT
											   student_teacher_id, name, teacher_id
                       FROM roster;";
			SqlDao dao = SqlDao;
			return dao.GetList<Person>(dao.GetSQLiteCommand(query));
		}

		public Person GetById(long id)
		{
			string query = @"SELECT
  										   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE student_teacher_id = @id;";

			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@id", id));
			return dao.GetSingle<Person>(command);
		}

		public Person Insert(Person t)
		{
			throw new Exception();

//      SqlDao dao = SqlDao;
//      string sql = @"insert into [roster]
//										   (name, teacher_id)
//										 values
//										   (@name, @teacher_id);
//										 select last_insert_rowid();";
//      SQLiteCommand command = dao.GetSQLiteCommand(sql);
//      command.Parameters.Add(dao.CreateParameter("@name", t.Name, 64));
//      command.Parameters.Add(dao.CreateParameter("@teacher_id", t.TeacherId));

//      t.StudentTeacherId = (long)dao.ExecuteScalar(command);
		}

		public Person Update(Person t)
		{
			throw new Exception();
//      SqlDao dao = SqlDao;
//      string sql = @"update [roster]
//										 set name=@name, teacher_id=@teacher_id
//										 where student_teacher_id=@student_teacher_id;";
//      SQLiteCommand command = dao.GetSQLiteCommand(sql);
//      command.Parameters.Add(dao.CreateParameter("@name", t.Name, 64));
//      command.Parameters.Add(dao.CreateParameter("@teacher_id", t.TeacherId));
//      command.Parameters.Add(dao.CreateParameter("@student_teacher_id", t.StudentTeacherId));
//      dao.ExecuteNonQuery(command);			
		}

		public void Delete(long id)
		{
			SqlDao dao = SqlDao;
			string sql = @"delete from [roster]
										 where student_teacher_id = @student_teacher_id;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@student_teacher_id", id));
			dao.ExecuteNonQuery(command);
		}

		public void Delete(Person t)
		{
			throw new Exception();

//			this.Delete(t.StudentTeacherId);
		}

		public void Truncate()
		{
			SqlDao dao = new SqlDao();
			string sql = @"delete from roster;
                     delete from sqlite_sequence where name='roster';";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			dao.ExecuteNonQuery(command);
		}
	}
}
